﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;


using Excel = Microsoft.Office.Interop.Excel;


using System.Diagnostics;
using System.IO;
using System.Windows.Forms;

namespace baitaptuan5
{
    public class ReportDB
    {
        #region Bien

        #endregion
        //static public void find(String findText, String replaceText, Word.Application word, Word.Document doc, object missing,String file)
        //{        // Create the Word application and declare a document
          
        //    System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
        //    System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
        //    // Everything that goes to the interop must be an object
        //    object fileName = file;

        //    // Open the Word document.
        //    // Pass the "missing" object defined above to all optional

        //    // parameters. All parameters must be of type object,
        //    // and passed by reference.
        //    doc = word.Documents.Open(ref fileName,
        //    ref missing, ref missing, ref missing, ref missing,
        //    ref missing, ref missing, ref missing, ref missing,
        //    ref missing, ref missing, ref missing, ref missing,
        //    ref missing, ref missing, ref missing);

        //    // Activate the document
        //    doc.Activate();

        //    // Loop through the StoryRanges (sections of the Word doc)
        //    foreach (Word.Range tmpRange in doc.StoryRanges)
        //    {
        //        // Set the text to find and replace
        //        tmpRange.Find.Text = findText;
        //        tmpRange.Find.Replacement.Text = replaceText;

        //        // Set the Find.Wrap property to continue (so it doesn't
        //        // prompt the user or stop when it hits the end of
        //        // the section)
        //        tmpRange.Find.Wrap = Word.WdFindWrap.wdFindContinue;

        //        // Declare an object to pass as a parameter that sets
        //        // the Replace parameter to the "wdReplaceAll" enum
        //        object replaceAll = Word.WdReplace.wdReplaceAll;

        //        // Execute the Find and Replace -- notice that the
        //        // 11th parameter is the "replaceAll" enum object
        //        tmpRange.Find.Execute(ref missing, ref missing, ref missing,
        //        ref missing, ref missing, ref missing, ref missing,
        //        ref missing, ref missing, ref missing, ref replaceAll,
        //        ref missing, ref missing, ref missing, ref missing);
        //    }

        //    // Save the changes
        //    doc.Save();

        //    // Close the doc and exit the app
        //    doc.Close(ref missing, ref missing, ref missing);
        //    // word.Application.Quit(ref missing, ref missing, ref missing);
        //    System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
        //}
        static public bool exportDataToExcel(string tieude, System.Data.DataTable dt)
        {
            bool result = false;
            //khoi tao cac doi tuong Com Excel de lam viec
            System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            Microsoft.Office.Interop.Excel.Application xlApp;
            
            Excel.Worksheet xlSheet;
            Excel.Workbook xlBook;
            //doi tuong Trống để thêm  vào xlApp sau đó lưu lại sau
            object missValue = System.Reflection.Missing.Value;
            //khoi tao doi tuong Com Excel moi
            xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlBook = xlApp.Workbooks.Add(missValue);
            
            //su dung Sheet dau tien de thao tac
            xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1);
            //không cho hiện ứng dụng Excel lên để tránh gây đơ máy
            xlApp.Visible = false;
            int socot=dt.Columns.Count;
            int sohang=dt.Rows.Count;
            int i,j;

                       SaveFileDialog f = new SaveFileDialog();
          
            if (f.ShowDialog() == DialogResult.OK)
            {

                //set thuoc tinh cho tieu de
                xlSheet.get_Range("A1", Convert.ToChar(socot + 65) + "1").Merge(false);
                Excel.Range caption = xlSheet.get_Range("A1", Convert.ToChar(socot + 65) + "1");
                caption.Select();
                caption.FormulaR1C1 = tieude;
                //căn lề cho tiêu đề
                caption.HorizontalAlignment = Excel.Constants.xlCenter;
                caption.Font.Bold = true;
                caption.VerticalAlignment = Excel.Constants.xlCenter;
                caption.Font.Size = 15;
                //màu nền cho tiêu đề
                caption.Interior.ColorIndex = 20;
                caption.RowHeight = 30;
                //set thuoc tinh cho cac header
                Excel.Range header = xlSheet.get_Range("A2", Convert.ToChar(socot + 65) + "2");
                header.Select();

                header.HorizontalAlignment = Excel.Constants.xlCenter;
                header.Font.Bold = true;
                header.Font.Size = 10;
                //điền tiêu đề cho các cột trong file excel
                for (i = 0; i < socot; i++)
                    xlSheet.Cells[2, i + 2] = dt.Columns[i].ColumnName;
                //dien cot stt
                xlSheet.Cells[2, 1] = "STT";
                for (i = 0; i < sohang; i++)
                    xlSheet.Cells[i + 3, 1] = i + 1;
                //dien du lieu vao sheet
                for (i = 0; i < sohang; i++)
                    for (j = 0; j < socot; j++)
                    {
                        xlSheet.Cells[i + 3, j + 2] = dt.Rows[i][j];

                    }
                //autofit độ rộng cho các cột 
                for (i = 0; i < sohang; i++)
                    ((Excel.Range)xlSheet.Cells[1, i + 1]).EntireColumn.AutoFit();

                //save file
                xlBook.SaveAs(f.FileName, Excel.XlFileFormat.xlWorkbookNormal, missValue, missValue, missValue, missValue, Excel.XlSaveAsAccessMode.xlExclusive, missValue, missValue, missValue, missValue, missValue);
                xlBook.Close(true, missValue, missValue);
                xlApp.Quit();

                // release cac doi tuong COM
                releaseObject(xlSheet);
                releaseObject(xlBook);
                releaseObject(xlApp);
                result = true;
            }
            System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
            return result;
        }
     static  public void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
               //throw new  Exception("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
   
    
    }
}
